﻿using System;
using System.Collections;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using Fuse8.DomainFramework.Common;
using Fuse8.DomainFramework.Domain;

namespace Fuse8.DomainFramework.DataAccess.SqlDatabase
{
	//TODO: Simplify this code
    internal class SqlQueryBuilder
    {
        private static readonly ConcurrentDictionary<string, SqlQueryInfo> _queries = new ConcurrentDictionary<string, SqlQueryInfo>();

        private static ExpressionToSqlTransformer GetTransformer(SqlConnection connection, string alias = "")
        {
            var rulesService = new ExpressionToSqlTransformRulesService();
            var mappingService = new EntityToSqlMappingService(connection);
            return new ExpressionToSqlTransformer(rulesService, mappingService, alias);
        }

        public static SqlQueryInfo BuildDeleteQuery<TEntity>(SqlConnection connection, Expression<Func<TEntity, bool>> expression)
        {
            var queryKey = string.Format("{0}{1}DELETE{2}", connection.ConnectionString, typeof(TEntity).Name, expression);
            return _queries.GetOrAdd(queryKey, index =>
                {
                    const string queryTemplate = "DELETE FROM {0} WHERE {1}";

                    var whereStatement = expression != null ? GetTransformer(connection).GetWhereSqlFromExpression(expression) : new SqlQueryInfo();
                    var fromStatement = GenerateFromStatement<TEntity>(whereStatement.RelatedProperties, connection, "", false);

                    var deleteQuery = string.Format(queryTemplate, fromStatement, whereStatement.Query);
                    System.Diagnostics.Trace.TraceInformation(deleteQuery);
                    return new SqlQueryInfo { Query = deleteQuery, Parameters = whereStatement.Parameters };
                });
        }

        public static SqlQueryInfo BuildInsertQuery<TEntity>(SqlConnection connection)
        {
            var queryKey = string.Format("{0}{1}INSERT", connection.ConnectionString, typeof(TEntity).Name);
            return _queries.GetOrAdd(queryKey, index =>
                {
                    const string queryTemplate = "INSERT INTO {0} ({1}) VALUES ({2}) SELECT SCOPE_IDENTITY()";
                    var insertStatement = GenerateInsertStatement<TEntity>(connection);
                    var updateQuery = string.Format(queryTemplate, SqlEntityHelper.GetEntityTableName<TEntity>(), insertStatement.Query, GenerateInsertValuesStatement(insertStatement.Parameters));
                    return new SqlQueryInfo { Query = updateQuery, Parameters = insertStatement.Parameters.Union(insertStatement.Parameters).ToList() };
                });
        }

        public static SqlQueryInfo BuildUpdateQuery<TEntity>(SqlConnection connection, IFieldTracker fieldTracker)
        {
            var queryKey = string.Format("{0}{1}UPDATE", connection.ConnectionString, typeof(TEntity).Name);

            if (fieldTracker != null)
            {
                return !fieldTracker.GetUpdatedFields().Any()
                           ? null
                           : InternalBuildUpdateQuery<TEntity>(connection, fieldTracker);
            }

            return _queries.GetOrAdd(queryKey, index => InternalBuildUpdateQuery<TEntity>(connection, fieldTracker));
        }

        private static SqlQueryInfo InternalBuildUpdateQuery<TEntity>(SqlConnection connection, IFieldTracker fieldTracker)
        {
            const string queryTemplate = "UPDATE {0} SET {1} WHERE {2}";
            var whereStatement = GenerateUpdateWhereStatement<TEntity>(connection);
            var fullUpadteQuery = new StringBuilder();
            var infos = fieldTracker == null ? GenerateUpdateStatement<TEntity>(connection) : GenerateUpdateStatement<TEntity>(connection, fieldTracker.GetUpdatedFields());
            foreach (var info in infos)
            {
                var updateQuery = string.Format(queryTemplate, SqlEntityHelper.GetEntityTableName<TEntity>(), info.Query, whereStatement.Query);
                fullUpadteQuery.AppendFormat("{0} ", updateQuery);
            }
            return new SqlQueryInfo { Query = fullUpadteQuery.ToString(), Parameters = infos[0].Parameters.Union(whereStatement.Parameters).ToList() };
        }

	    public static SqlQueryInfo BuildFindQuery<TEntity>(SqlConnection connection, Expression<Func<TEntity, bool>> expression)
        {
            return BuildFindQuery(connection, expression, p => p);
        }

        public static SqlQueryInfo BuildFindQuery<TEntity>(SqlConnection connection, Expression<Func<TEntity, bool>> expression, Expression<Func<TEntity, object>> columnsExpression)
        {
            var queryKey = string.Format("{0}{1}FIND{2}{3}", connection.ConnectionString, typeof(TEntity).Name, expression, columnsExpression);
            return _queries.GetOrAdd(queryKey, index =>
                {
                    var queryTemplate = expression == null ? "SELECT {0} FROM {1}" : "SELECT {0} FROM {1} WHERE {2}";

                    var selectStatement = GenerateSelectStatement<TEntity>(AliasBuilder.DefaultEntityAlias, columnsExpression.Body, connection);
                    var whereStatement = expression != null ? GetTransformer(connection, AliasBuilder.DefaultEntityAlias).GetWhereSqlFromExpression(expression) : new SqlQueryInfo();
                    var relatedProperties = selectStatement.RelatedProperties ?? new RelatedPropertyCollection();
                    relatedProperties.AddCollection(whereStatement.RelatedProperties);

                    var fromStatement = GenerateFromStatement<TEntity>(relatedProperties, connection, AliasBuilder.DefaultEntityAlias);

                    whereStatement.Query = string.Format(queryTemplate, selectStatement.Query, fromStatement, whereStatement.Query);
                    System.Diagnostics.Trace.TraceInformation(whereStatement.Query);
                    return whereStatement;
                });
        }

        private static IEnumerable<SqlColumn> GetPropertyForSelect(MemberExpression expression, RelatedPropertyCollection relatedProperties, SqlConnection connection)
        {
            string propAlias;
            RelatedProperty relatedProperty;
            if (typeof(IEntityObject).IsAssignableFrom(expression.Member.GetMemberType()))
            {
                propAlias = AliasBuilder.BuildAlias(expression, AliasBuilder.DefaultEntityAlias);
                relatedProperty = AliasBuilder.BuildRelatedProperty(expression);
                if (relatedProperty != null)
                {
                    relatedProperties.Add(relatedProperty);
                }
                return GetAllPropertiesForSelect(expression.Member.GetMemberType(), propAlias, expression, connection);
            }
            relatedProperty = AliasBuilder.BuildRelatedProperty(expression.Expression);
            if (relatedProperty != null)
            {
                relatedProperties.Add(relatedProperty);
            }
            propAlias = AliasBuilder.BuildAlias(expression.Expression, AliasBuilder.DefaultEntityAlias);
			return SchemaBuilder.Current.GetColumns(expression.Expression.Type, connection)
                .Where(p => p.IsPrimaryKey || (string.IsNullOrEmpty(p.EntityPropertyName) ? p.SqlColumnName : p.EntityPropertyName) == expression.Member.Name)
                .Select(p => new SqlColumn
                                {
                                    SqlColumnName = string.Format("{0}.{1}", propAlias, p.SqlColumnName),
                                    EntityPropertyName = BuildFullPropertyName(expression.Expression as MemberExpression, string.IsNullOrEmpty(p.EntityPropertyName) ? p.SqlColumnName : p.EntityPropertyName)
                                });
        }

        private static IEnumerable<SqlColumn> GetAllPropertiesForSelect(Type entityType, string alias, MemberExpression expression, SqlConnection connection)
        {
            return SchemaBuilder.Current.GetColumns(entityType, connection).Select(p => new SqlColumn
            {
                SqlColumnName = string.Format("{0}.[{1}]", alias, p.SqlColumnName),
                EntityPropertyName = BuildFullPropertyName(expression, string.IsNullOrEmpty(p.EntityPropertyName) ? p.SqlColumnName : p.EntityPropertyName)
            });
        }

        private static string BuildFullPropertyName(MemberExpression expression, string propertyName)
        {
            var propertyPath = new List<string>();
            while (expression != null)
            {
                propertyPath.Add(expression.Member.Name);
                expression = expression.Expression as MemberExpression;
            }
            propertyPath.Reverse();
            propertyPath.Add(propertyName);
            return string.Join("_", propertyPath.ToArray());
        }

        private static SqlQueryInfo GenerateInsertStatement<TEntity>(SqlConnection connection)
        {
            var properties = SchemaBuilder.Current.GetColumns<TEntity>(connection).Where(p => !p.IsIdentity).ToList();
            var query = string.Join(",", properties.Select(p => string.Format("[{0}]", p.SqlColumnName)).ToArray());
			return new SqlQueryInfo { Query = query, Parameters = properties.Select(p => EntityParameterBuilder<TEntity>.BuildPropertyDelegate(p.EffectiveName)).ToList() };
        }

        private static string GenerateInsertValuesStatement(IEnumerable<SqlParameterInfo> parameters)
        {
            return string.Join(",", parameters.Select(p => string.Format("@{0}", p.Name)).ToArray());
        }

		private static List<SqlQueryInfo> GenerateUpdateStatement<TEntity>(SqlConnection connection, IEnumerable<string> updatedFields = null)
        {
            var result = new List<SqlQueryInfo>();
            var properties = SchemaBuilder.Current.GetColumns<TEntity>(connection).Where(p => !p.IsPrimaryKey).ToList();
			if (updatedFields != null)
			{
				properties = properties.Where(p => updatedFields.Contains(p.EffectiveName)).ToList();
			}
            var types = SchemaBuilder.Current.GetUpdateParts<TEntity>();
            string query;
			var parameters = properties.Select(p => EntityParameterBuilder<TEntity>.BuildPropertyDelegate(p.EffectiveName)).ToList();
            if (types == null || types.Count == 0)
            {
                query = string.Join(",", properties.Select(p => string.Format("[{0}] = @{1}", p.SqlColumnName, p.EffectiveName)).ToArray());
                result.Add(new SqlQueryInfo { Query = query, Parameters = parameters });
            }
            else
            {
                foreach (var type in types)
                {
                    var typeProperties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly).Select(p => p.Name);
                    var columns = properties.Where(p => typeProperties.Contains(p.EffectiveName)).ToList();
                    if (columns.Any())
                    {
                        query = string.Join(",",
                                            columns.Select(
                                                p => string.Format("[{0}] = @{1}", p.SqlColumnName, p.EffectiveName)).
                                                ToArray());
                        result.Add(new SqlQueryInfo {Query = query, Parameters = parameters});
                    }
                }
            }
            
            return result;
        }

        private static SqlQueryInfo GenerateUpdateWhereStatement<TEntity>(SqlConnection connection)
        {
            var properties = SchemaBuilder.Current.GetColumns<TEntity>(connection).Where(p => p.IsPrimaryKey).ToList();
            var query = string.Join(" AND ", properties.Select(p => string.Format("([{0}] = @{1})", p.SqlColumnName, p.EffectiveName)).ToArray());
			return new SqlQueryInfo { Query = query, Parameters = properties.Select(p => EntityParameterBuilder<TEntity>.BuildPropertyDelegate(p.EffectiveName)).ToList() };
        }

        private static SqlQueryInfo GenerateSelectStatement<TEntity>(string alias, Expression propertiesExpression, SqlConnection connection)
        {
            var result = new SqlQueryInfo { RelatedProperties = new RelatedPropertyCollection() };
            if (propertiesExpression.NodeType == ExpressionType.Convert)
            {
                propertiesExpression = ((UnaryExpression)propertiesExpression).Operand;
            }

            if (propertiesExpression is ParameterExpression)
            {
                result.Query = FormatColumnsForSelect(GetAllPropertiesForSelect(typeof(TEntity), alias, null, connection));
                return result;
            }

            if (propertiesExpression.NodeType == ExpressionType.MemberAccess)
            {
                var columns = GetPropertyForSelect((MemberExpression)propertiesExpression, result.RelatedProperties, connection);
                result.Query = FormatColumnsForSelect(columns);
                return result;
            }

            if (propertiesExpression.NodeType == ExpressionType.New)
            {
                var newExpression = (NewExpression)propertiesExpression;
                var columns = new List<SqlColumn>();
                foreach (var expr in newExpression.Arguments)
                {
                    var parameterExpression = expr as ParameterExpression;
                    if (parameterExpression != null)
                    {
                        columns.AddRange(GetAllPropertiesForSelect(typeof(TEntity), alias, null, connection));
                    }
                    else
                    {
                        var memberExpression = expr as MemberExpression;
                        if (memberExpression == null)
                        {
                            throw new NotSupportedException(string.Format("{0} expression isn't supported", expr));
                        }
                        columns.AddRange(GetPropertyForSelect(memberExpression, result.RelatedProperties, connection));
                    }
                }
                result.Query = FormatColumnsForSelect(columns.Distinct(new SqlColumnEqualityComparer()));
                return result;
            }

            throw new NotSupportedException(string.Format("{0} expression isn't supported", propertiesExpression));
        }

        private static string GenerateFromStatement<TEntity>(IEnumerable<RelatedProperty> collection, SqlConnection connection, string defaultAlias, bool nolock = true)
        {
            return string.Format("{0} {1} {2} {3}", SqlEntityHelper.GetEntityTableName<TEntity>(), defaultAlias, nolock ? "WITH(NOLOCK)" : "", GenerateJoinStatement<TEntity>(collection, defaultAlias, connection));
        }

        private static string GenerateJoinStatement<TEntity>(IEnumerable<RelatedProperty> collection, string alias, SqlConnection connection)
        {
            var result = "";
            if (collection != null)
            {
                foreach (var relatedProperty in collection)
                {
                    result += string.Format(" LEFT JOIN {0} {1} WITH(NOLOCK) ON {2} ", SqlEntityHelper.GetEntityTableName(relatedProperty.PropertyType), relatedProperty.Alias,
                                            GenerateJoin<TEntity>(alias, relatedProperty.Alias, relatedProperty.PropertyName, relatedProperty.PropertyType, connection));
                    result += GenerateJoinStatement<TEntity>(relatedProperty.RelatedProperties, relatedProperty.Alias, connection);
                }
            }
            return result;
        }

        private static string GenerateJoin<T>(string alias, string relatedAlias, string propertyName, Type relatedPropertyType, SqlConnection connection)
        {
            var relateEntityKeys = SchemaBuilder.Current.GetColumns(relatedPropertyType, connection).Where(p => p.IsPrimaryKey);
            return string.Join(" AND ", relateEntityKeys.Select(p => string.Format("{0}.{1} = {2}.{3}", alias, GetColumnForJoin<T>(propertyName, p.EffectiveName), relatedAlias, p.SqlColumnName)).ToArray());
        }

        private static string GetColumnForJoin<T>(string propertName, string defualtPostfix)
        {
            //todo: add related column name to this method as well so it would possible to make joins by several columns
            var name = SqlEntityHelper.GetRelatedPropertyColumnName<T>(propertName);
            if (string.IsNullOrEmpty(name))
            {
                name = propertName + defualtPostfix;
            }

            return name;
        }

        private static string FormatColumnsForSelect(IEnumerable<SqlColumn> columns)
        {
            return string.Join(", ", columns.Select(p => string.Format("{0} [{1}]", p.SqlColumnName, p.EffectiveName)).ToArray());
        }
    }
}